-- zad 1 -- Write a SQL query to find the names and salaries of the employees that take the minimal salary in the company. Use a nested SELECT statement.
select e.FirstName, e.LastName, e.Salary
from Employees e 
where e.Salary IN 
  (select MIN(Salary)
   from Employees)
-- zad 1 --

-- zad 2 -- Write a SQL query to find the names and salaries of the employees that have a salary that is up to 10% higher than the minimal salary for the company.

select FirstName, LastName, Salary
from Employees
where Salary < 
(select MIN(Salary) * 1.1
 from Employees)
-- zad 2 --

-- zad 3 -- Write a SQL query to find the full name, salary and department of the employees that take the minimal salary in their department. Use a nested SELECT statement.
select e.FirstName + ' ' + e.LastName FullName, e.Salary, d.Name
from Employees e
  join Departments d
  on d.DepartmentID = e.DepartmentID
where e.Salary = 
 (select MIN(e1.Salary)
  from Employees e1
  where e1.DepartmentID = e.DepartmentID)
 order by e.DepartmentID
-- zad 3 --

-- zad 4 -- Write a SQL query to find the average salary in the department #1.
select AVG(Salary) as [Average salary department #1]
from Employees e
where e.DepartmentID = 1
-- zad 4 --
 
-- zad 5 -- Write a SQL query to find the average salary  in the "Sales" department.
select AVG(Salary) as [Average salary department #1]
from Employees e
where e.DepartmentID = 
  (select DepartmentID
   from Departments
   where Name = 'Sales')
-- zad 5 --

-- zad 6 -- Write a SQL query to find the number of employees in the "Sales" department.
select COUNT(*)
from Employees 
where DepartmentID =
  (select DepartmentID
   from Departments
   where Name = 'Sales')
-- zad 6 --  

-- zad 7 -- Write a SQL query to find the number of all employees that have manager.
select COUNT(ManagerID)
from Employees
--where ManagerID is not null
-- zad 7 --

-- zad 8 -- Write a SQL query to find the number of all employees that have no manager.
select COUNT(*) - COUNT(ManagerID)
from Employees

select COUNT(*)
from Employees
where ManagerID is null
-- 8 --

-- 9 -- Write a SQL query to find all departments and the average salary for each of them.
select AVG(e.Salary) [Average salary], d.Name
from Employees e
  join Departments d
  on d.DepartmentID = e.DepartmentID
group by e.DepartmentID, d.Name
-- 9 -- 

-- 10 -- Write a SQL query to find the count of all employees in each department and for each town.
select COUNT(*) as [Count], d.Name, t.Name
from Employees e
  join Departments d
  on e.DepartmentID = d.DepartmentID
    join Addresses a
    on a.AddressID = e.AddressID
      join Towns t
      on t.TownID = a.TownID
group by d.DepartmentID, d.Name, t.Name
-- 10 --

-- 11 -- Write a SQL query to find all managers that have exactly 5 employees. Display their first name and last name.
select COUNT(*) as Cnt
from Employees e
where e.ManagerID IN
  (select m.EmployeeID
   from Employees m)
group by e.ManagerID
having COUNT(*) = 5

select COUNT(*) as Cnt, m.FirstName, m.LastName
from Employees e
  join Employees m
  ON e.ManagerID = m.EmployeeID  
group by m.FirstName, m.LastName
having COUNT(*) = 5
-- 11 -- 

-- 12 -- Write a SQL query to find all employees along with their managers. For employees that do not have manager display the value "(no manager)".
select e.FirstName, e.LastName, isnull(m.FirstName, 'no manager')
from Employees e
  left join Employees m 
  ON e.ManagerID = m.EmployeeID
-- 12 -- 

-- 13 -- Write a SQL query to find the names of all employees whose last name is exactly 5 characters long. Use the built-in LEN(str) function.
select FirstName, LastName
from Employees
where LEN(LastName) = 5
-- 13 -- 

-- 14 -- Write a SQL query to display the current date and time in the following format "day.month.year hour:minutes:seconds:milliseconds". Search in  Google to find how to format dates in SQL Server.
select CONVERT(VARCHAR, GETDATE(),104) + ' ' + CONVERT(VARCHAR, GETDATE(),114)
-- 14 -- 

-- 15 -- Write a SQL statement to create a table Users. Users should have username, password, 
-- full name and last login time. Choose appropriate data types for the table fields. 
-- Define a primary key column with a primary key constraint. Define the primary key column as 
--identity to facilitate inserting records. Define unique constraint to avoid repeating usernames. 
-- Define a check constraint to ensure the password is at least 5 characters long. -- 

Create table Users (
  UserID int IDENTITY,
  UserName nvarchar(100) UNIQUE NOT NULL,
  Pass nvarchar(20) NOT NULL,
  LastLogIn DateTime,
  FullName nvarchar(100)
  CONSTRAINT PK_Users PRIMARY KEY(UserID),  
  CONSTRAINT Check_Pass CHECK(LEN(Pass) >= 5)  
)
-- 15 -- 

-- 16 -- Write a SQL statement to create a view that displays the users from the Users table that have been 
--in the system today. Test if the view works correctly. --
--CREATE VIEW [Users Logged Today] AS
--SELECT UserName FROM Users
--WHERE CONVERT(VARCHAR, LastLogIn, 101) = Convert(varchar,GETDATE(), 101)
-- 16 -- 

-- 17 -- Write a SQL statement to create a table Groups. 
-- Groups should have unique name (use unique constraint). Define primary key and identity column.
CREATE TABLE Groups (
  GroupID int IDENTITY,
  GroupName varchar(30) UNIQUE NOT NULL,
  CONSTRAINT PK_Groups PRIMARY KEY(GroupID)
)
-- 17 --

-- 18 -- Write a SQL statement to add a column GroupID to the table Users. 
--Fill some data in this new column and as well in the Groups table. 
--Write a SQL statement to add a foreign key constraint between tables Users and Groups tables. --
ALTER TABLE Users ADD GroupID int

ALTER TABLE Users 
ADD CONSTRAINT FK_Users_Groups 
FOREIGN KEY (GroupID) 
REFERENCES Groups(GroupID)
-- 18 -- 

-- 19 -- Write SQL statements to insert several records in the Users and Groups tables.
INSERT INTO Groups 
values ('Boss')

INSERT INTO Groups 
values ('Boss1')

INSERT INTO Groups 
values ('Boss2')

INSERT INTO Users (FullName,UserName,Pass,GroupID)
values ('Kamen Ivanov','thudnerlizard','reinstall',1)

INSERT INTO Users (FullName,UserName,Pass,GroupID)
values ('Ivan Ivanov','p1ndl3sk1n','turbo_gazar',1)

INSERT INTO Users (FullName,UserName,Pass,GroupID)
values ('Georgi Popov','gosho','parolagosho',2)

INSERT INTO Users (FullName,UserName,Pass,GroupID)
values ('Petur Petrov','pesho','parolapesho',3)

-- 19 -- 

-- 20 -- Write SQL statements to update some of the records in the Users and Groups tables.
update Users
set Pass = 'turbo_gazar'
where UserName = 'p1ndl3sk1n'
-- 20 --

-- 21 -- Write SQL statements to delete some of the records from the Users and Groups tables.
DELETE FROM Users
where LastLogIn is null
-- 21 -- 

-- 22 -- Write SQL statements to insert in the Users table the names of all employees from the Employees table. 
--Combine the first and last names as a full name. For username use the first letter of 
--the first name + the last name (in lowercase). Use the same for the password, and NULL for last login time.
insert into Users(FullName, UserName,Pass)
  (select 
	e.FirstName + ' ' + e.LastName,
    Lower(LEFT(FirstName,1) + LastName),
    Lower(LEFT(FirstName,1) + LastName)
    from Employees e
    where LEN(Lower(LEFT(FirstName,1) + LastName)) >= 5 AND (Lower(LEFT(FirstName,1) + LastName) <> 'ahill'))
-- 22 --

-- 23 -- Write a SQL statement that changes the password to NULL for all users that have not been in the 
-- system since 10.03.2010.
ALTER TABLE Users ALTER COLUMN Pass nvarchar(20) NULL

update Users
set Pass = NULL
where LastLogIn < CONVERT(datetime,'10.03.2010',103)
-- 23 -- 

-- 24 -- Write a SQL statement that deletes all users without passwords (NULL password).
Delete from Users
where Pass is NULL
-- 24 -- 

-- 25 -- Write a SQL query to display the average employee salary by department and job title.
select AVG(e.Salary), e.JobTitle, d.Name
from Employees e
  join Departments d
  ON e.DepartmentID = d.DepartmentID
group by d.Name, e.JobTitle
-- 25 -- 

-- 26 -- Write a SQL query to display the minimal employee salary by department and job title along 
--with the name of some of the employees that take it.
select MIN(e.Salary), e.JobTitle, d.Name
from Employees e
  join Departments d
  ON e.DepartmentID = d.DepartmentID
group by d.Name, e.JobTitle
order by e.JobTitle
-- 26 -- 

-- 27 -- Write a SQL query to display the town where maximal number of employees work.
select TOP 1 COUNT(*),t.Name
from Employees e
  join Addresses a 
  ON e.AddressID = a.AddressID
    join Towns t
    ON t.TownID = a.TownID
group by t.TownID, t.Name
order by COUNT(*) DESC
-- 27 --  

-- 28 -- Write a SQL query to display the number of managers from each town.
select COUNT(*), t.Name
from Employees e
  join Employees m
  ON e.ManagerID = m.EmployeeID
    join Addresses a
    ON m.AddressID = a.AddressID
      join Towns t
      ON a.TownID = t.TownID
group by t.Name, e.ManagerID
order by t.Name

select COUNT(*)
from Employees e
  join Employees m
  ON e.ManagerID = m.EmployeeID
group by e.ManagerID
-- 28 -- 



USE [TelerikAcademy]
GO
/****** Object:  Trigger [dbo].[InsertWorkHours]    Script Date: 06/22/2010 19:40:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[InsertWorkHours] ON [dbo].[WorkHours]
FOR INSERT
AS 
 insert into WorkHoursLogs ( WorkHourID, Command, OldComment, NewComment)
 (select WorkHourID,'Insert', null, Comments from Inserted)
 
 
 
 
 
 USE [TelerikAcademy]
GO
/****** Object:  Trigger [dbo].[UpdateWorkHours]    Script Date: 06/22/2010 19:40:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[UpdateWorkHours] ON [dbo].[WorkHours]
FOR UPDATE
AS
  insert into WorkHoursLogs(WorkHourID, Command, OldComment, NewComment)
  (select d.WorkHourID,'Update', d.Comments, i.Comments from Deleted d
     join Inserted i
     ON i.WorkHourID = d.WorkHourID)